﻿Imports DTO
Imports System.Data.OleDb

Public Class SachDAO
    Public Shared Function SoLuongTon(ByVal phieuNhap As PHieuNhapSach) As Double
        Dim kq As Double
        Dim connection As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select SoLuong from SACH where TenSach=? and MaTheLoaiSach=? and TacGia=?"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, connection)

        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters("@TenSach").Value = phieuNhap.TenSach

        cmd.Parameters.Add("@MaTheLoaiSach", OleDbType.Integer)
        cmd.Parameters("@MaTheLoaiSach").Value = TheLoaiSachDAO.LayMa(phieuNhap.TheLoai)

        cmd.Parameters.Add("@TacGia", OleDbType.WChar)
        cmd.Parameters("@TacGia").Value = phieuNhap.TacGia
        kq = cmd.ExecuteScalar()

        connection.Close()

        Return kq
    End Function
    Public Shared Function LaySoTienKhachHangNo(ByVal KhachHangDTO As BangKhachHangDTO) As Double
        Try


            Dim kq As Double
            Dim connect As OleDbConnection = DataProvider.ConnectDB()
            Dim sql As String = "select SoTienNo from KHACHHANG where TenKhachHang =? and DiaChi=? and DienThoai=? and Email =?"
            Dim cmd As OleDbCommand = New OleDbCommand(sql, connect)
            cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            cmd.Parameters("@TenKhachHang").Value = KhachHangDTO.strTenKhachHang

            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = KhachHangDTO.strDiaChi

            cmd.Parameters.Add("@DienThoai", OleDbType.Double)
            cmd.Parameters("@DienThoai").Value = KhachHangDTO.DDienThoai

            cmd.Parameters.Add("@Email", OleDbType.WChar)
            cmd.Parameters("@Email").Value = KhachHangDTO.strEmail
            kq = cmd.ExecuteScalar()
            connect.Close()
            Return kq
        Catch ex As Exception
            Return -1
        End Try

    End Function

    Public Function LuuBangSach(ByVal TenSach As String, ByVal MaTheLoai As String, ByVal TacGia As String, ByVal Giasach As Double, ByVal SoLuong As Double, ByVal ngayNhap As DateTime) As Integer
        'Dim cmd As OleDbCommand = New OleDbCommand()
        'cmd.Connection = DataProvider.ConnectDB()
        'cmd.CommandText = "insert into SACH(TenSach,MaTheLoaiSach,TacGia,GiaSach,SoLuong,NgayNhap) values(@tensach,@maTheloai,@Tacgia,@giasach,@soluong,@NgayNhap)"
        'Dim para As OleDbParameter = cmd.Parameters.Add("@tensach", OleDbType.WChar)
        'para.Value = TenSach
        'para = cmd.Parameters.Add("@maTheLoai", OleDbType.Integer)
        'para.Value = TheLoaiSachDAO.LayMa(MaTheLoai)

        'para = cmd.Parameters.Add("@Tacgia", OleDbType.WChar)
        'para.Value = TacGia
        'para = cmd.Parameters.Add("@giasach", OleDbType.Double)
        'para.Value = Giasach
        'para = cmd.Parameters.Add("@soluong", OleDbType.Double)
        'para.Value = SoLuong
        'para = cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        'para.Value = ngayNhap
        'cmd.ExecuteNonQuery()
        'cmd.CommandText = "select @@IDENTITY"
        'Return cmd.ExecuteScalar()
        Dim Connection As OleDbConnection = DataProvider.ConnectDB()
        Dim sql1 As String = "select MaSach from SACH where TenSach=? and MaTheLoaiSach=? and TacGia=?"
        Dim cmd1 As OleDbCommand = New OleDbCommand(sql1, Connection)

        cmd1.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd1.Parameters("@TenSach").Value = TenSach

        cmd1.Parameters.Add("@MaTheLoaiSach", OleDbType.Integer)
        cmd1.Parameters("@MaTheLoaiSach").Value = TheLoaiSachDAO.LayMa(MaTheLoai)

        cmd1.Parameters.Add("@TacGia", OleDbType.WChar)
        cmd1.Parameters("@TacGia").Value = TacGia
        Dim kq As Double = cmd1.ExecuteScalar()

        If kq <> 0 Then
            Dim strsql As String = "Update SACH set SoLuong = SoLuong + ? where MaSach =? "
            Dim cmd As OleDbCommand = New OleDbCommand(strsql, Connection)
            cmd.Parameters.Add("@SoLuong", OleDbType.Double)
            cmd.Parameters("@SoLuong").Value = SoLuong

            cmd.Parameters.Add("@MaSach", OleDbType.Integer)
            cmd.Parameters("@MaSach").Value = kq

            Dim tam As Integer = cmd.ExecuteNonQuery()


        Else
            Dim sql As String = "insert into SACH(TenSach,MaTheLoaiSach,TacGia,GiaSach,SoLuong,NgayNhap) values(@tensach,@maTheloai,@Tacgia,@giasach,@soluong,@NgayNhap)"
            Dim cmd As OleDbCommand = New OleDbCommand(sql, Connection)
            Dim para As OleDbParameter = cmd.Parameters.Add("@tensach", OleDbType.WChar)
            para.Value = TenSach
            para = cmd.Parameters.Add("@maTheLoai", OleDbType.Integer)
            para.Value = TheLoaiSachDAO.LayMa(MaTheLoai)

            para = cmd.Parameters.Add("@Tacgia", OleDbType.WChar)
            para.Value = TacGia
            para = cmd.Parameters.Add("@giasach", OleDbType.Double)
            para.Value = Giasach
            para = cmd.Parameters.Add("@soluong", OleDbType.Double)
            para.Value = SoLuong
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
            para.Value = ngayNhap
            cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            Return cmd.ExecuteScalar()
        End If

        Return kq



       

    End Function
    ' Lay ra ten tat cac cac cuon sach
    Public Shared Function LayTenSach() As List(Of BangSachDTO)
        Dim BangSach As List(Of BangSachDTO) = New List(Of BangSachDTO)
        Dim cmd As OleDbConnection = DataProvider.ConnectDB()
        Dim sql = " Select TenSach From Sach  Group By TenSach"
        Dim connect As OleDbCommand = New OleDbCommand(sql, cmd)
        Dim rederd As OleDbDataReader = connect.ExecuteReader()
        While rederd.Read
            Dim DongSAch As BangSachDTO = New BangSachDTO()
            DongSAch.strTenSach = rederd.GetString(0)
            BangSach.Add(DongSAch)
        End While
        cmd.Close()
        Return BangSach
    End Function
    Public Shared Function LayBangSach() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim bui As OleDbDataAdapter = New OleDbDataAdapter("select * from SACH", cnn)
        bui.Fill(dt)
        cnn.Close()
        Return dt
    End Function
   
    ' truyen vao ten sach va the loai lay ra gia sach
    Public Shared Function LayGiaSach(ByVal TenSach As String, ByVal matheloai As Integer) As List(Of BangSachDTO)
        Dim danhSach As List(Of BangSachDTO) = New List(Of BangSachDTO)
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select * from SACH where TenSach= ?and MaTheLoaiSach=? "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, cnn)
        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters("@TenSach").Value = TenSach
        cmd.Parameters.Add("@MaTheLoaiSach", OleDbType.Integer)
        cmd.Parameters("@MaTheLoaiSach").Value = Integer.Parse(matheloai)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        While reader.Read
            Dim SachDTo As BangSachDTO = New BangSachDTO()
            SachDTo.dGiaSach = reader.GetDouble(4)
            SachDTo.iMaSach = reader.GetInt32(0)
            danhSach.Add(SachDTo)
        End While

        cnn.Close()
        Return danhSach
    End Function
   
End Class
